【Redshift】クエリパターンとパフォーマンスから学ぶCompound SortKeyとInterleaved SortKeyのユースケース
こんにちは。DA事業本部の春田です。
先日、RedshiftのAdvisor機能より、こんなアラートが出ていました。同じような経験はございますか？
Initialize Interleaved Sort Keys Recomendation Run VACUUM REINDEX, as a superuser, on tables with inactive interleaved sort keys.
= VACUUM REINDEXを実行して、インターリーブソートキーを初期化してください。
Replace Single Column Interleaved Sort Keys Recomendation Recreate 5 tables to use a single-column compound sort key.
= 一つのカラムにしか設定していないインターリーブソートキーは、複合キーで作り直してください。
どちらも Interleaved Sort Key に関するアラートです。Interleaved Sort Keyは複数のカラムに対して均等な関係のインデックスを貼ることができる便利さがゆえに、とりあえず設定しておくにはもってこいのソートキーですが、結果的に運用上のパフォーマンスに悪影響を及ぼしている場合があります。しかしながら、このAdvisorの Recommendation に記載されている対処法はあくまでも一例であり、実際の運用方針やテーブル設計に沿わない場合もあります。ソートキー設計を根本から見直していく必要があるでしょう。
今回、Compound Sort KeyとInterleaved Sort Keyの特徴をまとめた上で、クエリごとにパフォーマンスにどのような違いが出るのか一つずつ検証してみました。ソートキー設計はかなり複雑で骨の折れる作業だと思います。本記事が一つの指針として参考になれば幸いです。
※2020/01/20: JOINのみ再検証を行いました。内部でパフォーマンスの改善があったからか、ほぼ分散キーが適切に設定しているかどうかが一番影響が大きくなっています。ソートキーはMarge Joinを狙いたい時ぐらいにしか、重要度がなさそうです。
目次
- Compound Sort KeyとInterleaved Sort Keyの特徴
- Compound Sort Key（複合キー）
- Interleaved Sort Key（インターリーブソートキー）
- 検証結果の結論
- 検証前の予備知識とサンプルテーブル
- クエリの検証
- ORDER BY
- GROUP BY
- WHERE
- JOIN
- Merge Joinの条件
- 参照
Compound Sort KeyとInterleaved Sort Keyの特徴
Compound Sort KeyとInterleaved Sort Keyには、以下のような細かな（細かすぎる）特徴があります。
Compound Sort Key（複合キー）
Compound Sort KeyはRedshiftのデフォルトのソート形式で、1つ以上のカラムから構成することができます。一般的に ORDER BY 、 GROUP BY やWindow関数の PARTION BY に置いて大きな効果を発揮します。
Compound Sort Keyの設計において最も重要な点は カラムの順番 です。1番目のカラムが プライマリ 、2番目のカラムが セカンダリ と呼ばれ、プライマリは単独でも使えますが、セカンダリ以降はその前のカラムと一緒に使用しなければほとんど効果を発揮することができません。例えば、以下のようなCompound Sort Keyを持つテーブルで ORDER BY をする場合、 dateid は単独で ORDER BY しても問題ありませんが、 eventid は dateid と併用して ORDER BY しなければ大幅に性能が落ちるということです。
CREATE TABLE sales_csk ( salesid integer not null, listid integer not null, sellerid integer not null, buyerid integer not null, eventid integer not null, dateid smallint not null, qtysold smallint not null, pricepaid decimal(8,2), commission decimal(8,2), saletime timestamp ) DISTSTYLE KEY DISTKEY (dateid) COMPOUND SORTKEY (dateid, eventid);
また、Compound Sort Keyの大きなメリットの一つに、特定の条件下において Merge Join がクエリプランで採択されるという点です。Merge JoinはRedshiftのJOIN演算子の中で最も高速な演算方法であり、今回行った検証でも著しく高いパフォーマンスを発揮しました。ただし、Merge Joinを実行するための条件はかなり難しく、データやテーブルが複雑になるほど達成が困難です。このMerge Joinの条件を満たせない場合は Hash Join が採択され、それゆえ大概の JOIN がHash Joinで行われています。（クロス結合や一部の不等結合では Nested Loop が採択されます）
Merge Joinの条件
INNER JOINとOUTER JOINのみで使用される。FULL JOINでは使われない- 両テーブルの結合カラムが 分散キー であり、かつ Compound Sort Keyのプライマリ を含んでいること
- 80%以上ソート済みのテーブルであること
その他、列の圧縮率を向上させたい場合にもCompound Sort Keyは有効です。
Interleaved Sort Key（インターリーブソートキー）
対するInterleaved Sort Keyも1つ以上のカラムから構成することができますが、 カラムの重要度が等しく、それぞれを単独で使用しても効果を発揮することができる 点が最大の特徴です。また、Interleaved Sort Keyのカラムを WHERE で選択的に制限する場合や、8バイト以上のPrefixのカラムに対してソートキーを設定する場合には、Compound Sort Keyよりも高いパフォーマンスが期待できます。
Interleaved Sort Keyの性能を維持するための条件として、定期的に VACUUM REINDEX を実行していく必要があります。 VACUUM REINDEX では通常の VACUUM FULL 処理の前に、インターリーブソートキー列の分散を分析して行の再調節を行う処理が加わり、その分実行時間が長くなります。そのため日々のバッチ処理やDBの運用方針とバッティングしないよう、よりシビアにスケジュールを組んでいく必要があります。運用上、この VACUUM REINDEX が定期的に実行できない場合は、IDやタイムスタンプといった レコード数、カーディナリティが単調に増加していくカラム に対しては、Interleaved Sort Keyを設定することは避けるべきです。
また、Interleaved Sort Keyは「スライスごとに1MBのブロックを複数必要とする大きさのテーブルで効果的」という特徴があるのですが、裏を返すとデータ量がそこまで大きくないテーブルに対しては顕著な効果を発揮しません。（ WHERE 句が多用される場合は別です。）高々レコード数が1000万件ぐらいのテーブルに対しては、チューニング・テクニックの影響よりもRedshiftのパワーの方が勝ってしまうので、効果が実感しづらいかと思います。個人的に、レコード数1億件を超えた辺りからInterleaved Sort Keyの性能が右肩上がりに上昇していくイメージがあります。
ちなみに VACUUM REINDEX をかけるべきかどうかの判断基準として、SVV_INTERLEAVED_COLUMNSのinterleaved_skewという数値があります。interleaved_skewの値が1.4以上の場合、VACUUM REINDEX によりパフォーマンスが向上すると言われています。
-- interleaved_skewを確認するクエリ例 SELECT DISTINCT sic.tbl as tbl_id, sti.schema as schema_name, stp.name as table_name, sti.tbl_rows, sic.col, sic.interleaved_skew, sic.last_reindex FROM svv_interleaved_columns sic LEFT JOIN svv_table_info sti ON sic.tbl = sti.table_id LEFT JOIN stv_tbl_perm stp ON sic.tbl = stp.id WHERE interleaved_skew > 1.4 ORDER BY schema_name, table_name, col;
Compound Sort KeyもInterleaved Sort Keyも一長一短であり、テーブル間の関係やSQLが複雑になればなるほど、どちらの方が適切なのかわからなくなってきます。そこで今回はシンプルなクエリパターンを通して、２つのソートキーのユースケースを検証していきたいと思います。
検証結果の結論
検証の部分がかなり長いので先に結論から申しますと、バッチ処理等で扱っているデータ量が1億件に満たない場合は以下のような方針がベターだと感じました。
- Compound Sort Keyベースで設計することを考え、「プライマリ・セカンダリの位置」と「SQL（JOINやWHERE）」の関係性をすり合わせていく
- JOINを使用しているテーブルに対しては、分散キーにCompound Sort Keyのプライマリを指定できるかどうか考える → Merge Joinできれば大幅なパフォーマンス向上につながる可能性
- 実際に運用してみて、明らかにボトルネックがありそうなテーブルに関しては、Interleaved Sort Keyを適用してみる
データ量が1億件を超える場合は、Interleaved Sort Keyの特性も生かせるので、よりシビアなソートキー設計が求められます。こちらに関しては、考慮すべき順番を一概に定められませんでした。
- プライマリもセカンダリも平等に扱いたいテーブルに対しては、積極的にInterleaved Sort Keyを設定していくとパフォーマンスの向上が見込める
- 1億件を超えるテーブルでは
VACUUM REINDEXの負荷が重くなるため、全てのテーブルでInterleaved Sort Keyを使うということは避けたい - Interleaved Sort Keyを使うテーブルには、
INSERTではなくCOPYによる全件洗替（Deep Copy）の運用を考えてみる → Deep Copy中はVACUUM処理と違って同時更新を実行できないが、ロード時に自動的にインターリーブインデックスが作成されるため、VACUUM REINDEXの実行を抑えられる - などなど……
上の方針案は、ドキュメントや検証結果から体感で導き出された一案であり、絶対案ではありません。あらかじめご理解ください。また、この結論は複雑かつ固定されたバッチ処理を行うテーブルに対してのソートキー設計方針であり、クライアントで参照されるような分析用のテーブルでは、よく使うクエリに合わせて設計するのがベストです。
より機械的にソートキーを設計したい場合は、AWSJシバタツさんのスライドがものすごく解りやすいので、合わせてご参照ください。
検証前の予備知識とサンプルテーブル
今回の検証では、ソートキーなし、Compound Sort Key、Interleaved Sort Keyをそれぞれ設定したサンプルテーブルを用意し、クエリ実行時間と以下のクエリプランから比較していきます。
コスト : EXPLAIN のクエリ実行計画の中で見れるクエリのパフォーマンスを測る相対的な指標。2つのピリオドで区切られた最初の数値が「最初の行を返す相対コスト」、末尾の数値が「クエリ完了までの相対コスト」を表す。（例 cost=131.97..133.41 ）
Join演算子 : テーブルをJOINする時にRedshift側で自動的に採択される演算方法。テーブル設計やデータの状態に基づく。Nested Loop、Hash Join (および Hash)、Merge Joinの3つの演算子があり、RedshiftではMerge Joinが一番高速な演算方法であるが、達成条件が厳しい。
集計演算子 : 集計関数および GROUP BY を含むクエリで自動的に採択される演算方法。 GROUP BY ではHashAggregateとGroupAggregateの2つあり、ソート処理を含む集計の場合GroupAggregateが採択される。
データの再分散 : テーブルをJOINする時に各ノード上のデータを他のノードに移動する手法。DS_BCAST_INNER、DS_DIST_ALL_NONE、DS_DIST_NONE、DS_DIST_INNER、DS_DIST_OUTER、DS_DIST_ALL_INNER、DS_DIST_BOTHの7種類存在し、余計なデータの移動がない手法（例えばDS_DIST_NONE）が好ましい。
検証テーブルは、ステップ 6: Amazon S3 のサンプルデータをロードする - Amazon Redshiftとステップ 1: テストデータセットを作成する - Amazon Redshiftのサンプルデータより作成しました。（なお、検証で言及しているレコード数の違いによるソートキー性能の差については、あまり厳密に調査しておりません。）
-- 365rows
CREATE TABLE date(
dateid smallint not null,
caldate date not null,
day character(3) not null,
week smallint not null,
month character(5) not null,
qtr character(5) not null,
year smallint not null,
holiday boolean default('N')
)
DISTSTYLE KEY DISTKEY(dateid)
ソートキーなし | COMPOUND SORTKEY (dateid) | INTERLEAVED SORTKEY (dateid)
;
-- 8,798rows
CREATE TABLE event(
eventid integer not null,
venueid smallint not null,
catid smallint not null,
dateid smallint not null,
eventname varchar(200),
starttime timestamp
)
DISTSTYLE KEY DISTKEY(eventid)
ソートキーなし | COMPOUND SORTKEY (eventid) | INTERLEAVED SORTKEY (eventid)
;
-- 172,456rows
CREATE TABLE sales(
salesid integer not null,
listid integer not null,
sellerid integer not null,
buyerid integer not null,
eventid integer not null,
dateid smallint not null,
qtysold smallint not null,
pricepaid decimal(8,2),
commission decimal(8,2),
saletime timestamp
)
DISTSTYLE KEY DISTKEY(dateid)
ソートキーなし | COMPOUND SORTKEY (dateid, eventid) | COMPOUND SORTKEY2 (eventid, dateid) | INTERLEAVED SORTKEY (dateid, eventid)
;
-- 1,400,000rows
CREATE TABLE part(
p_partkey INTEGER NOT NULL,
p_name VARCHAR(22) NOT NULL,
p_mfgr VARCHAR(6) NOT NULL,
p_category VARCHAR(7) NOT NULL,
p_brand1 VARCHAR(9) NOT NULL,
p_color VARCHAR(11) NOT NULL,
p_type VARCHAR(25) NOT NULL,
p_size INTEGER NOT NULL,
p_container VARCHAR(10) NOT NULL
)
DISTSTYLE KEY DISTKEY(p_partkey)
ソートキーなし | COMPOUND SORTKEY (p_partkey, p_size) | INTERLEAVED SORTKEY (p_partkey, p_size)
;
-- 3,000,000rows
CREATE TABLE customer(
c_custkey INTEGER NOT NULL,
c_name VARCHAR(25) NOT NULL,
c_address VARCHAR(25) NOT NULL,
c_city VARCHAR(10) NOT NULL,
c_nation VARCHAR(15) NOT NULL,
c_region VARCHAR(12) NOT NULL,
c_phone VARCHAR(15) NOT NULL,
c_mktsegment VARCHAR(10) NOT NULL
)
DISTSTYLE KEY DISTKEY(c_custkey)
ソートキーなし | COMPOUND SORTKEY (c_custkey, c_region) | INTERLEAVED SORTKEY (c_custkey, c_region)
;
-- 600,037,902rows
CREATE TABLE lineorder(
lo_orderkey INTEGER NOT NULL,
lo_linenumber INTEGER NOT NULL,
lo_custkey INTEGER NOT NULL,
lo_partkey INTEGER NOT NULL,
lo_suppkey INTEGER NOT NULL,
lo_orderdate INTEGER NOT NULL,
lo_orderpriority VARCHAR(15) NOT NULL,
lo_shippriority VARCHAR(1) NOT NULL,
lo_quantity INTEGER NOT NULL,
lo_extendedprice INTEGER NOT NULL,
lo_ordertotalprice INTEGER NOT NULL,
lo_discount INTEGER NOT NULL,
lo_revenue INTEGER NOT NULL,
lo_supplycost INTEGER NOT NULL,
lo_tax INTEGER NOT NULL,
lo_commitdate INTEGER NOT NULL,
lo_shipmode VARCHAR(10) NOT NULL
)
DISTSTYLE KEY DISTKEY(lo_custkey)
ソートキーなし | COMPOUND SORTKEY (lo_custkey, lo_partkey) | COMPOUND SORTKEY2 (lo_partkey, lo_custkey) | INTERLEAVED SORTKEY (lo_custkey, lo_partkey)
;
/*
nsk: ソートキーなし
csk: COMPOUND SORTKEY
csk2: COMPOUND SORTKEY(プライマリとセカンダリがcskと逆)
ilsk: INTERLEAVED SORTKEY
*/
copy date_nsk from 's3://awssampledbuswest2/tickit/date2008_pipe.txt'
credentials 'aws_iam_role=<iam-role-arn>'
delimiter '|' region 'us-west-2';
INSERT INTO date_csk SELECT * FROM date_nsk;
INSERT INTO date_ilsk SELECT * FROM date_nsk;
copy event_nsk from 's3://awssampledbuswest2/tickit/allevents_pipe.txt'
credentials 'aws_iam_role=<iam-role-arn>'
delimiter '|' timeformat 'YYYY-MM-DD HH:MI:SS' region 'us-west-2';
INSERT INTO event_csk SELECT * FROM event_nsk;
INSERT INTO event_ilsk SELECT * FROM event_nsk;
copy sales_nsk from 's3://awssampledbuswest2/tickit/sales_tab.txt'
credentials 'aws_iam_role=<iam-role-arn>'
delimiter '\t' timeformat 'MM/DD/YYYY HH:MI:SS' region 'us-west-2';
INSERT INTO sales_csk SELECT * FROM sales_nsk;
INSERT INTO sales_csk2 SELECT * FROM sales_nsk;
INSERT INTO sales_ilsk SELECT * FROM sales_nsk;
copy part_nsk from 's3://awssampledbuswest2/ssbgz/part'
credentials 'aws_iam_role=<iam-role-arn>'
gzip compupdate off region 'us-west-2';
INSERT INTO part_csk SELECT * FROM part_nsk;
INSERT INTO part_ilsk SELECT * FROM part_nsk;
copy customer_nsk from 's3://awssampledbuswest2/ssbgz/customer'
credentials 'aws_iam_role=<iam-role-arn>'
gzip compupdate off region 'us-west-2';
INSERT INTO customer_csk SELECT * FROM customer_nsk;
INSERT INTO customer_ilsk SELECT * FROM customer_nsk;
copy lineorder_nsk from 's3://awssampledbuswest2/ssbgz/lineorder'
credentials 'aws_iam_role=<iam-role-arn>'
gzip compupdate off region 'us-west-2';
INSERT INTO lineorder_csk SELECT * FROM lineorder_nsk;
INSERT INTO lineorder_csk2 SELECT * FROM lineorder_nsk;
INSERT INTO lineorder_ilsk SELECT * FROM lineorder_nsk;
データロード後、 VACUUM FULL または VACUUM REINDEX と ANALYZE を完了させ、 SET enable_result_cache_for_session = off; としてから検証に入っています。
クエリの検証
ORDER BY
-- レコード数1億件未満のテーブル
-- プライマリキーでORDER BY
SELECT dateid FROM sales ORDER BY dateid;
-- EXPLAIN
XN Merge (cost=1000000016724.67..1000000017155.81 rows=172456 width=2)
Merge Key: dateid
-> XN Network (cost=1000000016724.67..1000000017155.81 rows=172456 width=2)
Send to leader
-> XN Sort (cost=1000000016724.67..1000000017155.81 rows=172456 width=2)
Sort Key: dateid
-> XN Seq Scan on sales_nsk (cost=0.00..1724.56 rows=172456 width=2)
XN Merge (cost=0.00..1724.56 rows=172456 width=2)
Merge Key: dateid
-> XN Network (cost=0.00..1724.56 rows=172456 width=2)
Send to leader
-> XN Seq Scan on sales_csk (cost=0.00..1724.56 rows=172456 width=2)
XN Merge (cost=1000000016724.67..1000000017155.81 rows=172456 width=2)
Merge Key: dateid
-> XN Network (cost=1000000016724.67..1000000017155.81 rows=172456 width=2)
Send to leader
-> XN Sort (cost=1000000016724.67..1000000017155.81 rows=172456 width=2)
Sort Key: dateid
-> XN Seq Scan on sales_ilsk (cost=0.00..1724.56 rows=172456 width=2)
| # | ソートキーなし | COMPOUND SORT KEY | INTERLEAVED SORT KEY |
|---|---|---|---|
| 1 | 398ms | 351ms | 356ms |
| 2 | 376ms | 350ms | 362ms |
| 3 | 355ms | 344ms | 354ms |
| cost | 1000000016724.67..1000000017155.81 | 0.00..1724.56 | 1000000016724.67..1000000017155.81 |
実行時間はほぼ横ばい。コストはCOMPOUND SORT KEYが一番低く、ソートキーなしとINTERLEAVED SORT KEYは同じ結果に。
-- レコード数1億件未満のテーブル
-- セカンダリキーでORDER BY
SELECT eventid FROM sales ORDER BY eventid;
-- EXPLAIN
XN Merge (cost=1000000016724.67..1000000017155.81 rows=172456 width=4)
Merge Key: eventid
-> XN Network (cost=1000000016724.67..1000000017155.81 rows=172456 width=4)
Send to leader
-> XN Sort (cost=1000000016724.67..1000000017155.81 rows=172456 width=4)
Sort Key: eventid
-> XN Seq Scan on sales_nsk (cost=0.00..1724.56 rows=172456 width=4)
XN Merge (cost=1000000016724.67..1000000017155.81 rows=172456 width=4)
Merge Key: eventid
-> XN Network (cost=1000000016724.67..1000000017155.81 rows=172456 width=4)
Send to leader
-> XN Sort (cost=1000000016724.67..1000000017155.81 rows=172456 width=4)
Sort Key: eventid
-> XN Seq Scan on sales_csk (cost=0.00..1724.56 rows=172456 width=4)
XN Merge (cost=1000000016724.67..1000000017155.81 rows=172456 width=4)
Merge Key: eventid
-> XN Network (cost=1000000016724.67..1000000017155.81 rows=172456 width=4)
Send to leader
-> XN Sort (cost=1000000016724.67..1000000017155.81 rows=172456 width=4)
Sort Key: eventid
-> XN Seq Scan on sales_ilsk (cost=0.00..1724.56 rows=172456 width=4)
| # | ソートキーなし | COMPOUND SORT KEY | INTERLEAVED SORT KEY |
|---|---|---|---|
| 1 | 362ms | 346ms | 355ms |
| 2 | 363ms | 352ms | 349ms |
| 3 | 355ms | 365ms | 364ms |
| cost | 1000000016724.67..1000000017155.81 | 1000000016724.67..1000000017155.81 | 1000000016724.67..1000000017155.81 |
実行時間はほぼ横ばい。コストも同じであった。
-- レコード数1億件未満のテーブル
-- プライマリとセカンダリでORDER BY
SELECT dateid, eventid FROM sales ORDER BY dateid, eventid;
-- EXPLAIN
XN Merge (cost=1000000016724.67..1000000017155.81 rows=172456 width=6)
Merge Key: dateid, eventid
-> XN Network (cost=1000000016724.67..1000000017155.81 rows=172456 width=6)
Send to leader
-> XN Sort (cost=1000000016724.67..1000000017155.81 rows=172456 width=6)
Sort Key: dateid, eventid
-> XN Seq Scan on sales_nsk (cost=0.00..1724.56 rows=172456 width=6)
XN Merge (cost=0.00..1724.56 rows=172456 width=6)
Merge Key: dateid, eventid
-> XN Network (cost=0.00..1724.56 rows=172456 width=6)
Send to leader
-> XN Seq Scan on sales_csk (cost=0.00..1724.56 rows=172456 width=6)
XN Merge (cost=1000000016724.67..1000000017155.81 rows=172456 width=6)
Merge Key: dateid, eventid
-> XN Network (cost=1000000016724.67..1000000017155.81 rows=172456 width=6)
Send to leader
-> XN Sort (cost=1000000016724.67..1000000017155.81 rows=172456 width=6)
Sort Key: dateid, eventid
-> XN Seq Scan on sales_ilsk (cost=0.00..1724.56 rows=172456 width=6)
| # | ソートキーなし | COMPOUND SORT KEY | INTERLEAVED SORT KEY |
|---|---|---|---|
| 1 | 438ms | 417ms | 424ms |
| 2 | 437ms | 425ms | 439ms |
| 3 | 445ms | 431ms | 449ms |
| cost | 1000000016724.67..1000000017155.81 | 0.00..1724.56 | 1000000016724.67..1000000017155.81 |
実行時間はほぼ横ばい。コストはCOMPOUND SORT KEYが一番低く、ソートキーなしとINTERLEAVED SORT KEYは同じ結果に。
-- レコード数1億件以上のテーブル
-- プライマリでORDER BY
SELECT lo_custkey FROM lineorder ORDER BY lo_custkey LIMIT 10000;
-- EXPLAIN
XN Merge (cost=1000093487338.12..1000094987432.84 rows=600037888 width=4)
Merge Key: lo_custkey
-> XN Network (cost=1000093487338.12..1000094987432.84 rows=600037888 width=4)
Send to leader
-> XN Sort (cost=1000093487338.12..1000094987432.84 rows=600037888 width=4)
Sort Key: lo_custkey
-> XN Seq Scan on lineorder_nsk (cost=0.00..6000378.88 rows=600037888 width=4)
XN Merge (cost=0.00..6000378.88 rows=600037888 width=4)
Merge Key: lo_custkey
-> XN Network (cost=0.00..6000378.88 rows=600037888 width=4)
Send to leader
-> XN Seq Scan on lineorder_csk (cost=0.00..6000378.88 rows=600037888 width=4)
XN Merge (cost=1000093487338.12..1000094987432.84 rows=600037888 width=4)
Merge Key: lo_custkey
-> XN Network (cost=1000093487338.12..1000094987432.84 rows=600037888 width=4)
Send to leader
-> XN Sort (cost=1000093487338.12..1000094987432.84 rows=600037888 width=4)
Sort Key: lo_custkey
-> XN Seq Scan on lineorder_ilsk (cost=0.00..6000378.88 rows=600037888 width=4)
| # | ソートキーなし | COMPOUND SORT KEY | INTERLEAVED SORT KEY |
|---|---|---|---|
| 1 | 1.8s | 97ms | 1.4s |
| 2 | 1.7s | 93ms | 1.5s |
| 3 | 1.7s | 56ms | 1.5s |
| cost | 1000093487338.12..1000094987432.84 | 0.00..6000378.88 | 1000093487338.12..1000094987432.84 |
実行時間はCOMPOUND SORT KEYが最も短いが、INTERLEAVED SORT KEYもソートキーなしより短い。コストはCOMPOUND SORT KEYが一番低く、ソートキーなしとINTERLEAVED SORT KEYは同じ結果に。
-- レコード数1億件以上のテーブル
-- セカンダリでORDER BY
SELECT lo_partkey FROM lineorder ORDER BY lo_partkey LIMIT 10000;
-- EXPLAIN
XN Merge (cost=1000093487338.12..1000094987432.84 rows=600037888 width=4)
Merge Key: lo_partkey
-> XN Network (cost=1000093487338.12..1000094987432.84 rows=600037888 width=4)
Send to leader
-> XN Sort (cost=1000093487338.12..1000094987432.84 rows=600037888 width=4)
Sort Key: lo_partkey
-> XN Seq Scan on lineorder_nsk (cost=0.00..6000378.88 rows=600037888 width=4)
XN Merge (cost=1000093487338.12..1000094987432.84 rows=600037888 width=4)
Merge Key: lo_partkey
-> XN Network (cost=1000093487338.12..1000094987432.84 rows=600037888 width=4)
Send to leader
-> XN Sort (cost=1000093487338.12..1000094987432.84 rows=600037888 width=4)
Sort Key: lo_partkey
-> XN Seq Scan on lineorder_csk (cost=0.00..6000378.88 rows=600037888 width=4)
XN Merge (cost=1000093487338.12..1000094987432.84 rows=600037888 width=4)
Merge Key: lo_partkey
-> XN Network (cost=1000093487338.12..1000094987432.84 rows=600037888 width=4)
Send to leader
-> XN Sort (cost=1000093487338.12..1000094987432.84 rows=600037888 width=4)
Sort Key: lo_partkey
-> XN Seq Scan on lineorder_ilsk (cost=0.00..6000378.88 rows=600037888 width=4)
| # | ソートキーなし | COMPOUND SORT KEY | INTERLEAVED SORT KEY |
|---|---|---|---|
| 1 | 1.9s | 1.4s | 1.5s |
| 2 | 1.7s | 1.4s | 1.4s |
| 3 | 1.8s | 1.5s | 1.5s |
| cost | 1000093487338.12..1000094987432.84 | 1000093487338.12..1000094987432.84 | 1000093487338.12..1000094987432.84 |
COMPOUND SORT KEYとINTERLEAVED SORT KEY間に差はなく、どちらもソートキーなしより実行時間は短かい。コストは同じ。
-- レコード数1億件以上のテーブル
-- プライマリとセカンダリでORDER BY
SELECT lo_custkey, lo_partkey FROM lineorder ORDER BY lo_custkey, lo_partkey LIMIT 10000;
-- EXPLAIN
XN Merge (cost=1000093487338.12..1000094987432.84 rows=600037888 width=8)
Merge Key: lo_custkey, lo_partkey
-> XN Network (cost=1000093487338.12..1000094987432.84 rows=600037888 width=8)
Send to leader
-> XN Sort (cost=1000093487338.12..1000094987432.84 rows=600037888 width=8)
Sort Key: lo_custkey, lo_partkey
-> XN Seq Scan on lineorder_nsk (cost=0.00..6000378.88 rows=600037888 width=8)
XN Merge (cost=0.00..6000378.88 rows=600037888 width=8)
Merge Key: lo_custkey, lo_partkey
-> XN Network (cost=0.00..6000378.88 rows=600037888 width=8)
Send to leader
-> XN Seq Scan on lineorder_csk (cost=0.00..6000378.88 rows=600037888 width=8)
XN Merge (cost=1000093487338.12..1000094987432.84 rows=600037888 width=8)
Merge Key: lo_custkey, lo_partkey
-> XN Network (cost=1000093487338.12..1000094987432.84 rows=600037888 width=8)
Send to leader
-> XN Sort (cost=1000093487338.12..1000094987432.84 rows=600037888 width=8)
Sort Key: lo_custkey, lo_partkey
-> XN Seq Scan on lineorder_ilsk (cost=0.00..6000378.88 rows=600037888 width=8)
| # | ソートキーなし | COMPOUND SORT KEY | INTERLEAVED SORT KEY |
|---|---|---|---|
| 1 | 2.3s | 69ms | 1.7s |
| 2 | 2.4s | 66ms | 1.8s |
| 3 | 2.3s | 61ms | 1.7s |
| cost | 1000093487338.12..1000094987432.84 | 0.00..6000378.88 | 1000093487338.12..1000094987432.84 |
プライマリキーのみのORDER BYと同じ結果になったが、よりその差が顕著となった。コストはCOMPOUND SORT KEYが一番低く、ソートキーなしとINTERLEAVED SORT KEYは同じ結果に。
GROUP BY
-- レコード数1億件未満のテーブル -- プライマリキーでGROUP BY SELECT dateid, count(*) FROM sales GROUP BY dateid; -- EXPLAIN XN HashAggregate (cost=2586.84..2587.75 rows=364 width=2) -> XN Seq Scan on sales_nsk (cost=0.00..1724.56 rows=172456 width=2) XN GroupAggregate (cost=0.00..2587.71 rows=350 width=2) -> XN Seq Scan on sales_csk (cost=0.00..1724.56 rows=172456 width=2) XN HashAggregate (cost=2586.84..2587.75 rows=363 width=2) -> XN Seq Scan on sales_ilsk (cost=0.00..1724.56 rows=172456 width=2)
| # | ソートキーなし | COMPOUND SORT KEY | INTERLEAVED SORT KEY |
|---|---|---|---|
| 1 | 22ms | 21ms | 23ms |
| 2 | 24ms | 22ms | 24ms |
| 3 | 22ms | 21ms | 22ms |
| cost | 2586.84..2587.75 | 0.00..2587.71 | 2586.84..2587.75 |
| 集計演算子 | HashAggregate | GroupAggregate | HashAggregate |
実行時間はほぼ横ばい。コストはCOMPOUND SORT KEYの初期コストが他と比べて低いが、トータルコストはほぼ同じになっている。
-- レコード数1億件未満のテーブル -- セカンダリキーでGROUP BY SELECT eventid, count(*) FROM sales GROUP BY eventid; -- EXPLAIN XN HashAggregate (cost=2586.84..2606.31 rows=7787 width=4) -> XN Seq Scan on sales_nsk (cost=0.00..1724.56 rows=172456 width=4) XN HashAggregate (cost=2586.84..2606.42 rows=7834 width=4) -> XN Seq Scan on sales_csk (cost=0.00..1724.56 rows=172456 width=4) XN HashAggregate (cost=2586.84..2606.27 rows=7772 width=4) -> XN Seq Scan on sales_ilsk (cost=0.00..1724.56 rows=172456 width=4)
| # | ソートキーなし | COMPOUND SORT KEY | INTERLEAVED SORT KEY |
|---|---|---|---|
| 1 | 61ms | 62ms | 53ms |
| 2 | 53ms | 57ms | 60ms |
| 3 | 48ms | 48ms | 49ms |
| cost | 2586.84..2606.31 | 2586.84..2606.42 | 2586.84..2606.27 |
| 集計演算子 | HashAggregate | HashAggregate | HashAggregate |
実行時間はほぼ横ばい。微妙にバラつきがあるが、コストもほぼ同じ。
-- レコード数1億件未満のテーブル -- プライマリとセカンダリキーでGROUP BY SELECT dateid, eventid, count(*) FROM sales GROUP BY dateid, eventid; -- EXPLAIN XN HashAggregate (cost=3017.98..3061.09 rows=17246 width=6) -> XN Seq Scan on sales_nsk (cost=0.00..1724.56 rows=172456 width=6) XN GroupAggregate (cost=0.00..3061.09 rows=17246 width=6) -> XN Seq Scan on sales_csk (cost=0.00..1724.56 rows=172456 width=6) XN HashAggregate (cost=3017.98..3061.09 rows=17246 width=6) -> XN Seq Scan on sales_ilsk (cost=0.00..1724.56 rows=172456 width=6)
| # | ソートキーなし | COMPOUND SORT KEY | INTERLEAVED SORT KEY |
|---|---|---|---|
| 1 | 551ms | 437ms | 430ms |
| 2 | 683ms | 411ms | 586ms |
| 3 | 439ms | 659ms | 438ms |
| cost | 3017.98..3061.09 | 0.00..3061.09 | 3017.98..3061.09 |
| 集計演算子 | HashAggregate | GroupAggregate | HashAggregate |
実行時間はほぼ横ばい。コストはCOMPOUND SORT KEYの初期コストが他と比べて低いが、トータルコストはほぼ同じになっている。
-- レコード数1億件以上のテーブル -- プライマリキーでGROUP BY SELECT lo_custkey, count(*) FROM lineorder GROUP BY lo_custkey; -- EXPLAIN XN HashAggregate (cost=9000568.32..9004948.86 rows=1752214 width=4) -> XN Seq Scan on lineorder_nsk (cost=0.00..6000378.88 rows=600037888 width=4) XN GroupAggregate (cost=0.00..9005054.16 rows=1794335 width=4) -> XN Seq Scan on lineorder_csk (cost=0.00..6000378.88 rows=600037888 width=4) XN HashAggregate (cost=9000568.32..9005161.02 rows=1837079 width=4) -> XN Seq Scan on lineorder_ilsk (cost=0.00..6000378.88 rows=600037888 width=4)
| # | ソートキーなし | COMPOUND SORT KEY | INTERLEAVED SORT KEY |
|---|---|---|---|
| 1 | 13.2s | 4.6s | 8.5s |
| 2 | 13.3s | 4.6s | 8.3s |
| 3 | 13.3s | 4.6s | 8.4s |
| cost | 9000568.32..9004948.86 | 0.00..9005054.16 | 9000568.32..9005161.02 |
| 集計演算子 | HashAggregate | GroupAggregate | HashAggregate |
実行速度はCOMPOUND SORT KEYが最も速く、ついでINTERLEAVED SORT KEYが速い。トータルコストで見るとINTERLEAVED SORT KEY > COMPOUND SORT KEY > ソートキーなしとなっており、コスパが高いのはCOMPOUND SORT KEY。
-- レコード数1億件以上のテーブル -- セカンダリキーでGROUP BY SELECT lo_partkey, count(*) FROM lineorder GROUP BY lo_partkey; -- EXPLAIN XN HashAggregate (cost=9000568.32..9002981.02 rows=965078 width=4) -> XN Seq Scan on lineorder_nsk (cost=0.00..6000378.88 rows=600037888 width=4) XN HashAggregate (cost=9000568.32..9003137.57 rows=1027699 width=4) -> XN Seq Scan on lineorder_csk (cost=0.00..6000378.88 rows=600037888 width=4) XN HashAggregate (cost=9000568.32..9003314.96 rows=1098655 width=4) -> XN Seq Scan on lineorder_ilsk (cost=0.00..6000378.88 rows=600037888 width=4)
| # | ソートキーなし | COMPOUND SORT KEY | INTERLEAVED SORT KEY |
|---|---|---|---|
| 1 | 17.4s | 15.8s | 7.4s |
| 2 | 17.2s | 15.3s | 7.3s |
| 3 | 16.3s | 16.1s | 7.3s |
| cost | 9000568.32..9002981.02 | 9000568.32..9003137.57 | 9000568.32..9003314.96 |
| 集計演算子 | HashAggregate | HashAggregate | HashAggregate |
レコード数1億件以上のテーブルかつ、セカンダリのGROUP BYでようやくINTERLEAVED SORT KEYの長所が出た。コストは一番かかっているものの、実行時間が一番短い結果となった。
-- レコード数1億件以上のテーブル -- プライマリとセカンダリキーでGROUP BY SELECT lo_custkey, lo_partkey, count(*) FROM lineorder GROUP BY lo_custkey, lo_partkey LIMIT 10000; -- EXPLAIN XN HashAggregate (cost=10500663.04..10650672.51 rows=60003789 width=8) -> XN Seq Scan on lineorder_nsk (cost=0.00..6000378.88 rows=600037888 width=8) XN GroupAggregate (cost=0.00..10650672.51 rows=60003789 width=8) -> XN Seq Scan on lineorder_csk (cost=0.00..6000378.88 rows=600037888 width=8) XN HashAggregate (cost=10500663.04..10650672.51 rows=60003789 width=8) -> XN Seq Scan on lineorder_ilsk (cost=0.00..6000378.88 rows=600037888 width=8)
| # | ソートキーなし | COMPOUND SORT KEY | INTERLEAVED SORT KEY |
|---|---|---|---|
| 1 | 37.7s | 60ms | 33.7s |
| 2 | 36.8s | 67ms | 35.1s |
| 3 | 38.0s | 62ms | 34.6s |
| cost | 10500663.04..10650672.51 | 0.00..10650672.51 | 10500663.04..10650672.51 |
| 集計演算子 | HashAggregate | GroupAggregate | HashAggregate |
INTERLEAVED SORT KEYで2つのカラムでGROUP BYすると極端に遅くなった。逆にCOMPOUND SORT KEYは非常にパフォーマンスが高い。初期コストはCOMPOUND SORT KEYが一番低いが、トータルコストは同じである。
WHERE
-- レコード数1億件未満のテーブル
-- プライマリキーでWHERE
SELECT dateid FROM sales WHERE dateid IN ('2000', '2050', '1850');
-- EXPLAIN
XN Seq Scan on sales_nsk (cost=0.00..3017.98 rows=1395 width=2)
Filter: ((dateid = 1850::smallint) OR (dateid = 2000::smallint) OR (dateid = 2050::smallint))
XN Seq Scan on sales_csk (cost=0.00..24.44 rows=1397 width=2)
Filter: ((dateid = 1850::smallint) OR (dateid = 2000::smallint) OR (dateid = 2050::smallint))
XN Seq Scan on sales_ilsk (cost=0.00..3017.98 rows=1398 width=2)
Filter: ((dateid = 1850::smallint) OR (dateid = 2000::smallint) OR (dateid = 2050::smallint))
| # | ソートキーなし | COMPOUND SORT KEY | INTERLEAVED SORT KEY |
|---|---|---|---|
| 1 | 32ms | 35ms | 30ms |
| 2 | 25ms | 33ms | 27ms |
| 3 | 27ms | 27ms | 30ms |
| cost | 0.00..3017.98 | 0.00..24.44 | 0.00..3017.98 |
速度はほぼ横ばい。コストはCOMPOUND SORT KEYが一番低い。
-- レコード数1億件未満のテーブル -- セカンダリキーでWHERE SELECT eventid FROM sales WHERE eventid BETWEEN 10 AND 1000; -- EXPLAIN XN Seq Scan on sales_nsk (cost=0.00..2586.84 rows=19865 width=4) Filter: ((eventid <= 1000) AND (eventid >= 10)) XN Seq Scan on sales_csk (cost=0.00..2586.84 rows=19710 width=4) Filter: ((eventid <= 1000) AND (eventid >= 10)) XN Seq Scan on sales_ilsk (cost=0.00..2586.84 rows=19153 width=4) Filter: ((eventid <= 1000) AND (eventid >= 10))
| # | ソートキーなし | COMPOUND SORT KEY | INTERLEAVED SORT KEY |
|---|---|---|---|
| 1 | 73ms | 73ms | 75ms |
| 2 | 72ms | 70ms | 69ms |
| 3 | 65ms | 63ms | 67ms |
| cost | 0.00..2586.84 | 0.00..2586.84 | 0.00..2586.84 |
速度もコストも横ばいの結果となった。
-- レコード数1億件未満のテーブル -- プライマリキーとセカンダリキーでWHERE SELECT dateid, eventid FROM sales WHERE dateid BETWEEN 1000 AND 2000 AND eventid BETWEEN 10 AND 1000; -- EXPLAIN XN Seq Scan on sales_nsk (cost=0.00..3449.12 rows=9480 width=6) Filter: ((eventid <= 1000) AND (eventid >= 10) AND (dateid <= 2000) AND (dateid >= 1000)) XN Seq Scan on sales_csk (cost=0.00..1639.04 rows=9366 width=6) Filter: ((eventid <= 1000) AND (eventid >= 10) AND (dateid <= 2000) AND (dateid >= 1000)) XN Seq Scan on sales_ilsk (cost=0.00..3449.12 rows=9093 width=6) Filter: ((eventid <= 1000) AND (eventid >= 10) AND (dateid <= 2000) AND (dateid >= 1000))
| # | ソートキーなし | COMPOUND SORT KEY | INTERLEAVED SORT KEY |
|---|---|---|---|
| 1 | 64ms | 48ms | 65ms |
| 2 | 53ms | 49ms | 51ms |
| 3 | 46ms | 55ms | 54ms |
| cost | 0.00..3449.12 | 0.00..1639.04 | 0.00..3449.12 |
速度はほぼ横ばい。コストはCOMPOUND SORT KEYが一番低い。
-- レコード数1億件以上のテーブル
-- プライマリキーでWHERE
SELECT lo_custkey FROM lineorder WHERE lo_custkey IN ('123434', '123', '3415678');
-- EXPLAIN
XN Seq Scan on lineorder_nsk (cost=0.00..10500663.04 rows=1028 width=4)
Filter: ((lo_custkey = 123) OR (lo_custkey = 123434) OR (lo_custkey = 3415678))
XN Seq Scan on lineorder_csk (cost=0.00..17.56 rows=1004 width=4)
Filter: ((lo_custkey = 123) OR (lo_custkey = 123434) OR (lo_custkey = 3415678))
XN Seq Scan on lineorder_ilsk (cost=0.00..10500663.04 rows=980 width=4)
Filter: ((lo_custkey = 123) OR (lo_custkey = 123434) OR (lo_custkey = 3415678))
| # | ソートキーなし | COMPOUND SORT KEY | INTERLEAVED SORT KEY |
|---|---|---|---|
| 1 | 1.4s | 22ms | 143ms |
| 2 | 1.3s | 25ms | 135m |
| 3 | 1.3s | 24ms | 134ms |
| cost | 0.00..10500663.04 | 0.00..17.56 | 0.00..10500663.04 |
速度はCOMPOUND SORT KEYが最も速く、次いでINTERLEAVED SORT KEYが速い。コストはCOMPOUND SORT KEYが一番低い。
-- レコード数1億件以上のテーブル -- セカンダリキーでWHERE SELECT lo_partkey FROM lineorder WHERE lo_partkey BETWEEN 500 AND 1000; -- EXPLAIN XN Seq Scan on lineorder_nsk (cost=0.00..9000568.32 rows=309378 width=4) Filter: ((lo_partkey <= 1000) AND (lo_partkey >= 500)) XN Seq Scan on lineorder_csk (cost=0.00..9000568.32 rows=308579 width=4) Filter: ((lo_partkey <= 1000) AND (lo_partkey >= 500)) XN Seq Scan on lineorder_ilsk (cost=0.00..9000568.32 rows=305233 width=4) Filter: ((lo_partkey <= 1000) AND (lo_partkey >= 500))
| # | ソートキーなし | COMPOUND SORT KEY | INTERLEAVED SORT KEY |
|---|---|---|---|
| 1 | 1.3s | 2.3s | 575ms |
| 2 | 1.3s | 1.8s | 579ms |
| 3 | 1.3s | 2.3s | 594ms |
| cost | 0.00..9000568.32 | 0.00..9000568.32 | 0.00..9000568.32 |
GROUP BYの時と同じく、レコード数1億件以上のテーブルかつ、セカンダリキーでINTERLEAVED SORT KEYの長所が出た。COMPOUND SORT KEYに関しては、ソートなしよりも遅い結果となった。コストは同じである。
-- レコード数1億件以上のテーブル -- プライマリキーとセカンダリキーでWHERE SELECT lo_custkey, lo_partkey FROM lineorder WHERE lo_custkey BETWEEN 500 AND 2000 AND lo_partkey BETWEEN 500 AND 1000; -- EXPLAIN XN Seq Scan on lineorder_nsk (cost=0.00..12000757.76 rows=157 width=8) Filter: ((lo_custkey <= 2000) AND (lo_custkey >= 500) AND (lo_partkey <= 1000) AND (lo_partkey >= 500)) XN Seq Scan on lineorder_csk (cost=0.00..6017.71 rows=155 width=8) Filter: ((lo_custkey <= 2000) AND (lo_custkey >= 500) AND (lo_partkey <= 1000) AND (lo_partkey >= 500)) XN Seq Scan on lineorder_ilsk (cost=0.00..12000757.76 rows=151 width=8) Filter: ((lo_custkey <= 2000) AND (lo_custkey >= 500) AND (lo_partkey <= 1000) AND (lo_partkey >= 500))
| # | ソートキーなし | COMPOUND SORT KEY | INTERLEAVED SORT KEY |
|---|---|---|---|
| 1 | 1.6s | 25ms | 24ms |
| 2 | 1.6s | 23ms | 23ms |
| 3 | 1.7s | 23ms | 22ms |
| cost | 0.00..12000757.76 | 0.00..6017.71 | 0.00..12000757.76 |
COMPOUND SORT KEYもINTERLEAVED SORT KEYもほぼ同じくらい高速。GROUP BYと違って、WHEREはINTERLEAVED SORT KEYでも複数のカラムを選択してもパフォーマンスは落ちない。コストはCOMPOUND SORT KEYが一番低い。
JOIN
-- レコード数1億件未満のテーブル
-- プライマリキーでJOIN
SELECT s.dateid, s.eventid, s.salesid, d.caldate FROM sales_nsk s LEFT JOIN date d ON s.dateid = d.dateid;
-- EXPLAIN
XN Hash Left Join DS_DIST_NONE (cost=4.56..5609.38 rows=172456 width=14)
Hash Cond: ("outer".dateid = "inner".dateid)
-> XN Seq Scan on sales_nsk s (cost=0.00..1724.56 rows=172456 width=10)
-> XN Hash (cost=3.65..3.65 rows=365 width=6)
-> XN Seq Scan on date_nsk d (cost=0.00..3.65 rows=365 width=6)
XN Merge Left Join DS_DIST_NONE (cost=0.00..21.21 rows=740 width=14)
Merge Cond: ("outer".dateid = "inner".dateid)
-> XN Seq Scan on sales_csk s (cost=0.00..7.40 rows=740 width=10)
-> XN Seq Scan on date_csk d (cost=0.00..3.65 rows=365 width=6)
XN Hash Left Join DS_DIST_NONE (cost=4.56..28.61 rows=740 width=14)
Hash Cond: ("outer".dateid = "inner".dateid)
-> XN Seq Scan on sales_csk2 s (cost=0.00..7.40 rows=740 width=10)
-> XN Hash (cost=3.65..3.65 rows=365 width=6)
-> XN Seq Scan on date_csk d (cost=0.00..3.65 rows=365 width=6)
XN Hash Left Join DS_DIST_NONE (cost=4.56..29.26 rows=760 width=14)
Hash Cond: ("outer".dateid = "inner".dateid)
-> XN Seq Scan on sales_ilsk s (cost=0.00..7.60 rows=760 width=10)
-> XN Hash (cost=3.65..3.65 rows=365 width=6)
-> XN Seq Scan on date_ilsk d (cost=0.00..3.65 rows=365 width=6)
| # | ソートキーなし | COMPOUND SORT KEY | COMPOUND SORT KEY2 | INTERLEAVED SORT KEY |
|---|---|---|---|---|
| 1 | 681ms | 655ms | 687ms | 670ms |
| 2 | 682ms | 679ms | 685ms | 683ms |
| 3 | 665ms | 684ms | 677ms | 678ms |
| cost | 4.56..5609.38 | 0.00..21.21 | 4.56..28.61 | 4.56..29.26 |
| JOIN演算子 | Hash Left Join | Merge Left Join | Hash Left Join | Hash Left Join |
| データ再分散 | DS_DIST_NONE | DS_DIST_NONE | DS_DIST_NONE | DS_DIST_NONE |
速度は横ばいだが、演算の効率性にはかなり差が出ている。ソートキーなしにコストが最もかかっており、Merge Left Joinが採択されたCOMPOUND SORT KEYはコストが最も小さい。
-- レコード数1億件未満のテーブル
-- セカンダリキーでJOIN
SELECT s.dateid, s.eventid, s.salesid, e.eventname FROM sales s LEFT JOIN event e ON s.eventid = e.eventid;
-- EXPLAIN
XN Hash Left Join DS_BCAST_INNER (cost=2.50..664036693.70 rows=172456 width=328)
Hash Cond: ("outer".eventid = "inner".eventid)
-> XN Seq Scan on sales_nsk s (cost=0.00..1724.56 rows=172456 width=10)
-> XN Hash (cost=2.00..2.00 rows=200 width=322)
-> XN Seq Scan on event_nsk e (cost=0.00..2.00 rows=200 width=322)
XN Hash Right Join DS_DIST_INNER (cost=9.50..76000014.20 rows=760 width=328)
Inner Dist Key: s.eventid
Hash Cond: ("outer".eventid = "inner".eventid)
-> XN Seq Scan on event_csk e (cost=0.00..2.00 rows=200 width=322)
-> XN Hash (cost=7.60..7.60 rows=760 width=10)
-> XN Seq Scan on sales_csk s (cost=0.00..7.60 rows=760 width=10)
XN Hash Right Join DS_DIST_INNER (cost=9.50..76000014.20 rows=760 width=328)
Inner Dist Key: s.eventid
Hash Cond: ("outer".eventid = "inner".eventid)
-> XN Seq Scan on event_csk e (cost=0.00..2.00 rows=200 width=322)
-> XN Hash (cost=7.60..7.60 rows=760 width=10)
-> XN Seq Scan on sales_csk s (cost=0.00..7.60 rows=760 width=10)
XN Hash Right Join DS_DIST_INNER (cost=9.50..76000014.20 rows=760 width=328)
Inner Dist Key: s.eventid
Hash Cond: ("outer".eventid = "inner".eventid)
-> XN Seq Scan on event_ilsk e (cost=0.00..2.00 rows=200 width=322)
-> XN Hash (cost=7.60..7.60 rows=760 width=10)
-> XN Seq Scan on sales_ilsk s (cost=0.00..7.60 rows=760 width=10)
| # | ソートキーなし | COMPOUND SORT KEY | COMPOUND SORT KEY2 | INTERLEAVED SORT KEY |
|---|---|---|---|---|
| 1 | 699ms | 669ms | 676ms | 720ms |
| 2 | 752ms | 694ms | 689ms | 680ms |
| 3 | 691ms | 706ms | 696ms | 675ms |
| cost | 2.50..664036693.70 | 9.50..76000014.20 | 9.50..76000014.20 | 9.50..76000014.20 |
| JOIN演算子 | Hash Left Join | Hash Right Join | Hash Right Join | Hash Right Join |
| データ再分散 | DS_BCAST_INNER | DS_DIST_INNER | DS_DIST_INNER | DS_DIST_INNER |
速度は横ばいだが、唯一のMerge Left JoinかつDS_BCAST_INNERのソートキーなしのコストが高い。他は同じクエリ計画だった。
-- レコード数1億件未満のテーブル
-- プライマリとセカンダリキーでJOIN
SELECT s.dateid, s.eventid, s.salesid, e.eventname FROM sales s LEFT JOIN event e ON s.dateid = e.dateid AND s.eventid = e.eventid;
-- EXPLAIN
XN Hash Left Join DS_BCAST_INNER (cost=3.00..664071572.37 rows=172456 width=328)
Hash Cond: (("outer".eventid = "inner".eventid) AND ("outer".dateid = "inner".dateid))
-> XN Seq Scan on sales_nsk s (cost=0.00..1724.56 rows=172456 width=10)
-> XN Hash (cost=2.00..2.00 rows=200 width=324)
-> XN Seq Scan on event_nsk e (cost=0.00..2.00 rows=200 width=324)
XN Hash Right Join DS_DIST_OUTER (cost=11.40..332000018.41 rows=760 width=328)
Outer Dist Key: e.dateid
Hash Cond: (("outer".eventid = "inner".eventid) AND ("outer".dateid = "inner".dateid))
-> XN Seq Scan on event_csk e (cost=0.00..2.00 rows=200 width=324)
-> XN Hash (cost=7.60..7.60 rows=760 width=10)
-> XN Seq Scan on sales_csk s (cost=0.00..7.60 rows=760 width=10)
XN Hash Right Join DS_DIST_OUTER (cost=11.40..332000018.41 rows=760 width=328)
Outer Dist Key: e.dateid
Hash Cond: (("outer".eventid = "inner".eventid) AND ("outer".dateid = "inner".dateid))
-> XN Seq Scan on event_csk e (cost=0.00..2.00 rows=200 width=324)
-> XN Hash (cost=7.60..7.60 rows=760 width=10)
-> XN Seq Scan on sales_csk s (cost=0.00..7.60 rows=760 width=10)
XN Hash Right Join DS_DIST_OUTER (cost=11.40..332000018.41 rows=760 width=328)
Outer Dist Key: e.dateid
Hash Cond: (("outer".eventid = "inner".eventid) AND ("outer".dateid = "inner".dateid))
-> XN Seq Scan on event_csk e (cost=0.00..2.00 rows=200 width=324)
-> XN Hash (cost=7.60..7.60 rows=760 width=10)
-> XN Seq Scan on sales_csk s (cost=0.00..7.60 rows=760 width=10)
| # | ソートキーなし | COMPOUND SORT KEY | COMPOUND SORT KEY2 | INTERLEAVED SORT KEY |
|---|---|---|---|---|
| 1 | 557ms | 575ms | 574ms | 568ms |
| 2 | 565ms | 577ms | 561ms | 572ms |
| 3 | 592ms | 603ms | 603ms | 570ms |
| cost | 3.00..664071572.37 | 11.40..332000018.41 | 11.40..332000018.41 | 11.40..332000018.41 |
| JOIN演算子 | Hash Left Join | Hash Right Join | Hash Right Join | Hash Right Join |
| データ再分散 | DS_BCAST_INNER | DS_DIST_OUTER | DS_DIST_OUTER | DS_DIST_OUTER |
1つ前の結果とほぼ同じだが、プライマリのみよりもプライマリとセカンダリキーでJOINした方がコストが高くなってる。
-- レコード数1億件以上のテーブル
-- プライマリキーでJOIN
SELECT l.lo_custkey, l.lo_orderdate, c.c_name FROM lineorder l LEFT JOIN customer c ON l.lo_custkey = c.c_custkey LIMIT 10000;
-- EXPLAIN
XN Hash Left Join DS_DIST_NONE (cost=2.88..145509958.27 rows=600037888 width=63)
Hash Cond: ("outer".lo_custkey = "inner".c_custkey)
-> XN Seq Scan on lineorder_nsk l (cost=0.00..6000378.88 rows=600037888 width=8)
-> XN Hash (cost=2.30..2.30 rows=230 width=59)
-> XN Seq Scan on customer_nsk c (cost=0.00..2.30 rows=230 width=59)
XN Merge Left Join DS_DIST_NONE (cost=0.00..7501322.73 rows=600037888 width=63)
Merge Cond: ("outer".lo_custkey = "inner".c_custkey)
-> XN Seq Scan on lineorder_csk l (cost=0.00..6000378.88 rows=600037888 width=8)
-> XN Seq Scan on customer_csk c (cost=0.00..2.30 rows=230 width=59)
XN Hash Left Join DS_DIST_NONE (cost=2.88..145509965.91 rows=600037888 width=63)
Hash Cond: ("outer".lo_custkey = "inner".c_custkey)
-> XN Seq Scan on lineorder_csk2 l (cost=0.00..6000378.88 rows=600037888 width=8)
-> XN Hash (cost=2.30..2.30 rows=230 width=59)
-> XN Seq Scan on customer_csk c (cost=0.00..2.30 rows=230 width=59)
XN Hash Left Join DS_DIST_NONE (cost=2.88..145509981.34 rows=600037888 width=63)
Hash Cond: ("outer".lo_custkey = "inner".c_custkey)
-> XN Seq Scan on lineorder_ilsk l (cost=0.00..6000378.88 rows=600037888 width=8)
-> XN Hash (cost=2.30..2.30 rows=230 width=59)
-> XN Seq Scan on customer_ilsk c (cost=0.00..2.30 rows=230 width=59)
| # | ソートキーなし | COMPOUND SORT KEY | COMPOUND SORT KEY2 | INTERLEAVED SORT KEY |
|---|---|---|---|---|
| 1 | 89ms | 58ms | 65ms | 65ms |
| 2 | 73ms | 64ms | 75ms | 78ms |
| 3 | 84ms | 65ms | 63ms | 58ms |
| cost | 2.88..145509958.27 | 0.00..7501322.73 | 2.88..145509965.91 | 2.88..145509981.34 |
| JOIN演算子 | Hash Left Join | Merge Left Join | Hash Left Join | Hash Left Join |
| データ再分散 | DS_DIST_NONE | DS_DIST_NONE | DS_DIST_NONE | DS_DIST_NONE |
レコード数が1億件を超えると顕著に差が出る。速度はCOMPOUND SORT KEYが最も速く、ソートキーなしとINTERLEAVED SORT KEYが同率、COMPOUND SORT KEY2は唯一DS_BCAST_INNERが採択され、ソートキーなしより3倍近く遅くなった。
再検証ではなんと、レコード数1億件以上の方が1億件未満のテーブルよりも高速になっていた。内部のアルゴリズムが変わったのか、どのカラムを分散キーに指定すべきかがより重要になったのか……？ソートキーはもはやJOINに関係なさそうである。
-- レコード数1億件以上のテーブル
-- セカンダリキーでJOIN
SELECT l.lo_custkey, l.lo_orderdate, p.p_name FROM lineorder l LEFT JOIN part p ON l.lo_partkey = p.p_partkey LIMIT 10000;
-- EXPLAIN
XN Hash Left Join DS_BCAST_INNER (cost=3.25..329911925.59 rows=600037888 width=59)
Hash Cond: ("outer".lo_partkey = "inner".p_partkey)
-> XN Seq Scan on lineorder_nsk l (cost=0.00..6000378.88 rows=600037888 width=12)
-> XN Hash (cost=2.60..2.60 rows=260 width=55)
-> XN Seq Scan on part_nsk p (cost=0.00..2.60 rows=260 width=55)
XN Hash Left Join DS_BCAST_INNER (cost=3.25..329911904.27 rows=600037888 width=59)
Hash Cond: ("outer".lo_partkey = "inner".p_partkey)
-> XN Seq Scan on lineorder_csk l (cost=0.00..6000378.88 rows=600037888 width=12)
-> XN Hash (cost=2.60..2.60 rows=260 width=55)
-> XN Seq Scan on part_csk p (cost=0.00..2.60 rows=260 width=55)
XN Hash Left Join DS_BCAST_INNER (cost=3.25..329912008.52 rows=600037888 width=59)
Hash Cond: ("outer".lo_partkey = "inner".p_partkey)
-> XN Seq Scan on lineorder_csk2 l (cost=0.00..6000378.88 rows=600037888 width=12)
-> XN Hash (cost=2.60..2.60 rows=260 width=55)
-> XN Seq Scan on part_csk p (cost=0.00..2.60 rows=260 width=55)
XN Hash Left Join DS_BCAST_INNER (cost=3.25..329911898.94 rows=600037888 width=59)
Hash Cond: ("outer".lo_partkey = "inner".p_partkey)
-> XN Seq Scan on lineorder_ilsk l (cost=0.00..6000378.88 rows=600037888 width=12)
-> XN Hash (cost=2.60..2.60 rows=260 width=55)
-> XN Seq Scan on part_ilsk p (cost=0.00..2.60 rows=260 width=55)
| # | ソートキーなし | COMPOUND SORT KEY | COMPOUND SORT KEY2 | INTERLEAVED SORT KEY |
|---|---|---|---|---|
| 1 | 69ms | 86ms | 69ms | 80ms |
| 2 | 70ms | 96ms | 65ms | 75ms |
| 3 | 64ms | 65ms | 60ms | 68ms |
| cost | 3.25..329911925.59 | 3.25..329911904.27 | 3.25..329912008.52 | 3.25..329911898.94 |
| JOIN演算子 | Hash Left Join | Hash Left Join | Hash Left Join | Hash Left Join |
| データ再分散 | DS_BCAST_INNER | DS_BCAST_INNER | DS_BCAST_INNER | DS_BCAST_INNER |
Merge Left JoinしているCOMPOUND SORT KEY2が速度・コストともにダントツのパフォーマンスで、他はあまり差がない。
上と同様、やはり分散キーが適切ならJOINが速くなる模様。
-- レコード数1億件以上のテーブル
-- プライマリとセカンダリキーでJOIN
SELECT l.lo_custkey, l.lo_orderdate FROM lineorder l LEFT JOIN lineorder l2 ON l.lo_custkey = l2.lo_custkey AND l.lo_partkey = l2.lo_partkey LIMIT 10000;
-- EXPLAIN
XN Hash Left Join DS_DIST_NONE (cost=9000568.32..7230458601.30 rows=600037888 width=8)
Hash Cond: (("outer".lo_custkey = "inner".lo_custkey) AND ("outer".lo_partkey = "inner".lo_partkey))
-> XN Seq Scan on lineorder_nsk l (cost=0.00..6000378.88 rows=600037888 width=12)
-> XN Hash (cost=6000378.88..6000378.88 rows=600037888 width=8)
-> XN Seq Scan on lineorder_nsk l2 (cost=0.00..6000378.88 rows=600037888 width=8)
XN Merge Left Join DS_DIST_NONE (cost=0.00..18003367.66 rows=600037888 width=8)
Merge Cond: (("outer".lo_custkey = "inner".lo_custkey) AND ("outer".lo_partkey = "inner".lo_partkey))
-> XN Seq Scan on lineorder_csk l (cost=0.00..6000378.88 rows=600037888 width=12)
-> XN Seq Scan on lineorder_csk l2 (cost=0.00..6000378.88 rows=600037888 width=8)
XN Hash Left Join DS_DIST_NONE (cost=9000568.32..7230458594.08 rows=600037888 width=8)
Hash Cond: (("outer".lo_custkey = "inner".lo_custkey) AND ("outer".lo_partkey = "inner".lo_partkey))
-> XN Seq Scan on lineorder_csk2 l (cost=0.00..6000378.88 rows=600037888 width=12)
-> XN Hash (cost=6000378.88..6000378.88 rows=600037888 width=8)
-> XN Seq Scan on lineorder_csk l2 (cost=0.00..6000378.88 rows=600037888 width=8)
XN Hash Left Join DS_DIST_NONE (cost=9000568.32..7230458627.72 rows=600037888 width=8)
Hash Cond: (("outer".lo_custkey = "inner".lo_custkey) AND ("outer".lo_partkey = "inner".lo_partkey))
-> XN Seq Scan on lineorder_ilsk l (cost=0.00..6000378.88 rows=600037888 width=12)
-> XN Hash (cost=6000378.88..6000378.88 rows=600037888 width=8)
-> XN Seq Scan on lineorder_ilsk l2 (cost=0.00..6000378.88 rows=600037888 width=8)
| # | ソートキーなし | COMPOUND SORT KEY | COMPOUND SORT KEY2 | INTERLEAVED SORT KEY |
|---|---|---|---|---|
| 1 | 26.0s | 69ms | 25.3s | 24.7s |
| 2 | 25.9s | 65ms | 25.1s | 25.2s |
| 3 | 26.9s | 54ms | 25.2s | 25.0s |
| cost | 9000568.32..7230458601.30 | 0.00..18003367.66 | 9000568.32..7230458594.08 | 9000568.32..7230458627.72 |
| JOIN演算子 | Hash Left Join | Merge Left Join | Hash Left Join | Hash Left Join |
| データ再分散 | DS_DIST_NONE | DS_DIST_NONE | DS_DIST_OUTER | DS_DIST_NONE |
いい感じのテーブルがなかったため、同じテーブルをJOINしている。Merge Left Joinが採択されたCOMPOUND SORT KEYのみ100msを切った。
Merge Joinの条件
-- レコード数1億件以上のテーブル
-- プライマリキーでJOIN
SELECT l.lo_custkey, l.lo_orderdate, c.c_name FROM lineorder_csk l LEFT JOIN customer_csk c ON l.lo_custkey = c.c_custkey LIMIT 10000;
SELECT l.lo_custkey, l.lo_orderdate, c.c_name FROM lineorder_csk l LEFT JOIN (SELECT c_custkey, c_name FROM customer_csk ) c ON l.lo_custkey = c.c_custkey LIMIT 10000;
SELECT l.lo_custkey, l.lo_orderdate, c.c_name FROM lineorder_csk l LEFT JOIN customer_csk c ON l.lo_custkey::text = c.c_custkey::text LIMIT 10000;
-- EXPLAIN
XN Merge Left Join DS_DIST_NONE (cost=0.00..13538352.48 rows=600037888 width=30)
Merge Cond: ("outer".lo_custkey = "inner".c_custkey)
-> XN Seq Scan on lineorder_csk l (cost=0.00..6000378.88 rows=600037888 width=8)
-> XN Seq Scan on customer_csk c (cost=0.00..30000.00 rows=3000000 width=26)
XN Merge Left Join DS_DIST_NONE (cost=0.00..13538352.48 rows=600037888 width=30)
Merge Cond: ("outer".lo_custkey = "inner".c_custkey)
-> XN Seq Scan on lineorder_csk l (cost=0.00..6000378.88 rows=600037888 width=8)
-> XN Seq Scan on customer_csk (cost=0.00..30000.00 rows=3000000 width=26)
XN Hash Left Join DS_BCAST_INNER (cost=37500.00..6570354213173.60 rows=9000568320000 width=30)
Hash Cond: ((("outer".lo_custkey)::character varying)::text = (("inner".c_custkey)::character varying)::text)
-> XN Seq Scan on lineorder_csk l (cost=0.00..6000378.88 rows=600037888 width=8)
-> XN Hash (cost=30000.00..30000.00 rows=3000000 width=26)
-> XN Seq Scan on customer_csk c (cost=0.00..30000.00 rows=3000000 width=26)
| # | 加工なし | サブクエリ | データ型変更 |
|---|---|---|---|
| 1 | 62ms | 82ms | 1.2s |
| 2 | 99ms | 81ms | 1.2s |
| 3 | 80ms | 81ms | 1.2s |
| cost | 0.00..13538352.48 | 0.00..13538352.48 | 37500.00..6570354213173.60 |
| JOIN演算子 | Merge Left Join | Merge Left Join | Hash Left Join |
Merge Left Joinはサブクエリでも実行されるが、カラムを加工してしまうと採択されなくなる。
参照
- クエリプラン - Amazon Redshift
- The LOOP, HASH and MERGE Join Types
- VACUUM - Amazon Redshift
- インデックスを再生成するかどうかの決定 - Amazon Redshift
- ソートキーの選択 - Amazon Redshift
- ソート形式の比較 - Amazon Redshift
- 最良のソートキーの選択 - Amazon Redshift
- Amazon Redshift Engineering’s Advanced Table Design Playbook: Distribution Styles and Distribution Keys | AWS Big Data Blog
- Amazon Redshift Engineering’s Advanced Table Design Playbook: Compound and Interleaved Sort Keys | AWS Big Data Blog
- AWS Solutions Architect ブログ: Amazon Redshift テーブル設計詳細ガイド:Part 3 Compound and Interleaved Sort Keys（Compound と Interleaved ソートキー）
- ディープコピーを実行する - Amazon Redshift
- Amazon Redshift テーブル設計詳細ガイド
- カーディナリティについてまとめてみた - Qiita
- EXPLAIN - Amazon Redshift
- 未ソートリージョンのサイズを管理する - Amazon Redshift
- 【新機能】Amazon Redshift の Interleaved Sorting機能を試してみた ｜ DevelopersIO
- Amazon Redshift Interleaved Sortkey と VACUUM 実行のポイント ｜ DevelopersIO
- SVV_DISKUSAGE - Amazon Redshift
- SVL_QLOG - Amazon Redshift
- ステップ 1: テストデータセットを作成する - Amazon Redshift
- ステップ 6: Amazon S3 のサンプルデータをロードする - Amazon Redshift
![[レポート] データ分析作業におけるSnowflakeパフォーマンスの向上 #SnowflakeSummit](https://devio2023-media.developers.io/wp-content/uploads/2021/03/snowflake-logo-1200x630-1.png){kind=logo}
